﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SCRUM.Core.BusinessObjects;
using System.Data.Common;
using SCRUM.Core.Exceptions.Database;

namespace SCRUM.Core.DataProviders
{
    /// <summary>
    /// Privider for comunication with the database
    /// </summary>
    public partial class DBDataProvider : DataProvider
	{
        /// <summary>
        /// Gets the columns for the specified tab.
        /// </summary>
        /// <param name="tab">The specified tab.</param>
        /// <returns>List of columns for the specified tab.</returns>
        public override IList<WidgetColumn> GetColumns(WidgetTab tab)
        {
            IList<WidgetColumn> columns = new List<WidgetColumn>();
            try
            {
                DbParameter tabID = CreateParameter("tabID", tab.ID);

                connection.Open();
                DbDataReader data = GetVector("column/GetColumns", tabID);

                while (data.Read())
                {
                    WidgetColumn column = new WidgetColumn(
                        (int)data["ID"],
                        (int)data["ORDER"],
                        (int)data["WIDTH"]);
                    columns.Add(column);
                }
            }
            finally
            {
                connection.Close();
            }
            return columns; 
        }

        /// <summary>
        /// Gets the column for specified widget
        /// </summary>
        /// <param name="widget">The widget.</param>
        /// <returns>column which contains specified widget</returns>
        public override WidgetColumn GetColumn(Widget widget)
        {
            WidgetColumn widgetColumn;
            try
            {
                DbParameter dbWidgetID = CreateParameter("widgetID", widget.ID);
                connection.Open();
                DbDataReader data = GetVector("column/GetColumnForWidget", dbWidgetID);
                if(data.Read())
                {
                    widgetColumn = new WidgetColumn(
                        (int)data["ID"],
                        (int)data["ORDER"],
                        (int)data["WIDTH"]);
                }
                else
                {
                    throw new InconsistentDatabaseException("widget with specified ID does not exists");
                }
            }
            finally
            {
                connection.Close();
            }
            return widgetColumn;
        }


        /// <summary>
        /// Updates atributes of column
        /// </summary>
        /// <param name="widgetColumn">Column to update</param>
        private void updateColumn(WidgetColumn widgetColumn)
        {
            try
            {
                DbParameter dbTabID = CreateParameter("columnID", widgetColumn.ID);
                DbParameter dbOrder = CreateParameter("order", widgetColumn.Order);
                DbParameter dbWidth = CreateParameter("width", widgetColumn.Width);
                connection.Open();
                ExecuteNonQuery("column/UpdateColumn", dbTabID, dbOrder, dbWidth);
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Deletes the column.
        /// </summary>
        /// <param name="widgetColumn">The widget column to be deleted.</param>
        public override void DeleteColumn(WidgetColumn widgetColumn)
        {
            IList<WidgetColumn> columns = GetColumns(widgetColumn.Tab);
            try
            {
                //zmazem vsetky jeho widgety
                foreach (Widget widget in widgetColumn.Widgets)
                {
                    DeleteWidget(widget);
                }

                DbParameter dbWidgetColumnID = CreateParameter("widgetColumnID", widgetColumn.ID);
                connection.Open();
                ExecuteNonQuery("column/DeleteColumn", dbWidgetColumnID);
            }
            finally
            {
                connection.Close();
            }
            
            
            foreach (WidgetColumn w in columns)
            {
                if (w.Order > widgetColumn.Order)
                    w.Order -= 1;
                updateColumn(w);
            }

        }

        /// <summary>
        /// Adds the column into widget (as last one, counting from left to right)
        /// </summary>
        /// <param name="tab">The widget tab.</param>
        /// <paraparam name="before">Add column before widgetColumn or after</paraparam>
        /// <param name="widgetColumn">Represents column to add</param>
        public override void AddColumn(WidgetColumn widgetColumn, WidgetTab tab, bool before)
        {
            IList<WidgetColumn> columns = GetColumns(tab);
            foreach(WidgetColumn w in columns)
            {
                if (before)
                {
                    if (w.Order >= widgetColumn.Order)
                    {
                        w.Order += 1;
                        updateColumn(w);
                    }
                }
                else
                {
                    if (w.Order > widgetColumn.Order)
                    {
                        w.Order += 1;
                        updateColumn(w);
                    }
                }
            }

            try
            {
                DbParameter dbTabID = CreateParameter("tabID", tab.ID);
                if (!before) 
                {
                    widgetColumn.Order += 1;
                }
                DbParameter dbOrder = CreateParameter("order", widgetColumn.Order);
                DbParameter dbWidth = CreateParameter("width", widgetColumn.Width);
                connection.Open();
                ExecuteNonQuery("column/AddColumn", dbTabID, dbOrder, dbWidth);
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Copies the columns for tab.
        /// </summary>
        /// <param name="column">The column.</param>
        /// <param name="tabID">The tab ID.</param>
        /// <returns>ID of created column</returns>
        public override int CopyColumnForTab(WidgetColumn column, int tabID)
        {
            DbParameter dbtabID = CreateParameter("tabID", tabID);
            DbParameter dborder = CreateParameter("order", column.Order);
            DbParameter dbwidth = CreateParameter("width", column.Width);

            int newColumnID;

            try
            {
                connection.Open();                

                ExecuteNonQuery("column/AddColumn", dbtabID, dborder, dbwidth);
                
                newColumnID = Convert.ToInt32(GetScalar("column/GetLastInsertedColumnID"));
            }
            finally
            {
                connection.Close();
            }
            return newColumnID;
        }
    }
}
